/**
 * Created with IntelliJ IDEA.
 * User: Adi Polak & Idan Morad
 * Date: 17/11/13
 * Time: 16:57
 */

import java.io.*;
import java.sql.*;
import java.util.Vector;

import static java.lang.Class.forName;
import static java.lang.System.*;
import static java.sql.DriverManager.getConnection;

public class Assigment {

    /**
     * Returns a new Connection to the database.
     * @return the connection object
     * @throws ClassNotFoundException if ojdbc is not found.
     * @throws SQLException if connection to the database cannot be created.*/
    private static Connection createConnection() throws ClassNotFoundException, SQLException {
        forName("oracle.jdbc.driver.OracleDriver");
        String connectionURL = "jdbc:oracle:thin:@ora1.ise.bgu.ac.il:1521/ORACLE";
        return getConnection(connectionURL, "idanmora", "idan1403");
    }

    public static void fileToDataBase(String filePath){
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            BufferedReader br = new BufferedReader(new FileReader(filePath));
            String line, insert;
            insert = "INSERT INTO MEDIAITEMS(MEDIAITEMS.TITLE,MEDIAITEMS.\"PROD_YEAR\") VALUES(?,?)";
            conn = createConnection();
            ps =  conn.prepareStatement(insert);

            while((line = br.readLine()) != null){
                String[] data = line.split(",");
                String title = data[0];
                ps.setString(1,title);
                int year = Integer.parseInt(data[1]);
                ps.setInt(2, year);
                ps.execute();
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if(ps != null){
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void calculateSimilarity(){
        ResultSet rs = null;
        Statement st=null,cstmt=null;
        Connection conn = null;
        int maximalDist = 0;
        PreparedStatement ps = null, updatePs = null, psSim = null;

        try{
            conn = createConnection();
            cstmt = conn.createStatement();
            rs = cstmt.executeQuery("select MAXIMALDISTANCE() from dual");
            while(rs.next()){
                maximalDist= rs.getInt(1);
            }
            rs.close();

            Vector<Integer> midVector = new Vector<Integer>();
            st = conn.createStatement();
            String getTable="select MID from MEDIAITEMS";
            rs = st.executeQuery(getTable);
            while(rs.next()){
                 midVector.add(rs.getInt(1));
            }
            rs.close();

            String simCalc = "select SIMCALCULATION(?,?," + maximalDist + ") from dual";
            psSim = conn.prepareCall(simCalc);

            String insertSimilarity = "insert into similarity values(?,?,?)";
            ps = conn.prepareStatement(insertSimilarity);

            String update = "UPDATE similarity SET similarity=? WHERE mid1=? AND mid2=?";
            updatePs = conn.prepareStatement(update);
            float sim = 0;
            for (int i = 0; i < midVector.size(); i++){
                for (int j = i+1; j < midVector.size(); j++){
                    psSim.setInt(1,midVector.elementAt(i));
                    psSim.setInt(2,midVector.elementAt(j));
                    rs = psSim.executeQuery();
                    while (rs.next()){
                        sim = rs.getFloat(1);
                    }
                    rs.close();

                    ps.setInt(1,midVector.elementAt(i));
                    ps.setInt(2,midVector.elementAt(j));
                    ps.setFloat(3, sim);
                    try{
                        ps.executeUpdate();
                    }catch (SQLException e) {
                        if(!e.getMessage().contains("ORA-00001")){
                            throw e;
                        }
                        updatePs.setFloat(1, sim);
                        updatePs.setInt(2,midVector.elementAt(i));
                        updatePs.setInt(3, midVector.elementAt(j));
                        updatePs.executeUpdate();
                    }

                }
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try{
                if(psSim != null){
                    psSim.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try{
                if(st!=null){
                    st.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try{
                if(updatePs != null){
                    updatePs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try{
              if(cstmt != null){
                  cstmt.close();
              }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try{
                if(rs != null){
                    rs.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(ps != null){
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void printSimilarItems(long mid){
        String queryGetSimmilarItems = "SELECT TITLE FROM SIMILARITY, MEDIAITEMS " +
                "WHERE (SIMILARITY.SIMILARITY <> 0 AND SIMILARITY.MID1 = MEDIAITEMS.MID AND SIMILARITY.MID2="+mid+") " +
                "      OR " +
                "      (SIMILARITY.SIMILARITY <> 0 AND SIMILARITY.MID1 = "+mid+" AND SIMILARITY.MID2=MEDIAITEMS.MID) " +
                "ORDER BY SIMILARITY.SIMILARITY desc ";
        ResultSet rs;
        Statement st = null;
        Connection conn = null;

        try {
            conn = createConnection();
            st = conn.createStatement();

            rs = st.executeQuery(queryGetSimmilarItems);

            while(rs.next()){
                out.println(rs.getString(1));
            }
            rs.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if(st!=null){
                    st.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try{
                if(conn!=null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
